set hive.exec.dynamic.partition=true;            --  动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=500;         --  每天生成 60 个分区
set hive.exec.max.dynamic.partitions.pernode=500; --  每天生成 60 个分区
with electronic_package as (
    select package_number
         , max(center_code) as center_code --拆包网点
         , max(center_name) as center_name
    from jms_dwd.dwd_tab_electronic_package_list_base_dt
    where dt >= date_add('{{ execution_date | cst_ds }}', -30)
      and dt <= '{{ execution_date | cst_ds }}'
      and package_number is not null
    group by package_number
),
     load_base_ship as (
         select shipment_no
              , scan_site_code
              , scan_time
              , scan_user_code
              , scan_user
         from (
                  select shipment_no
                       , scan_site_code
                       , scan_time
                       , scan_user_code
                       , scan_user
                       , row_number() over (partition by shipment_no,scan_site_code order by scan_time desc ) as rn
                  from jms_dwd.dwd_tab_barscan_loading_base_dt
                  where dt >= date_add('{{ execution_date | cst_ds }}', -30)
                    and dt <= '{{ execution_date | cst_ds }}'
                    and shipment_no is not null
              ) a
         where rn = 1
     ),
     bagging_table as (
         select *
         from (
                  select waybill_no
                       , package_code
                       , scan_site_code
                       , scan_site
                       , scan_time
                       , row_number() over (partition by waybill_no,scan_site_code order by scan_time desc ) rnk
                  from jms_dwd.dwd_tab_barscan_bagging_base_dt
                  where dt >= date_add('{{ execution_date | cst_ds }}', -30)
                    and dt <= '{{ execution_date | cst_ds }}'
                    and waybill_no is not null
                    and package_code is not null
              )
         where rnk = 1
     ),
     network_whole as (
         select code
              , name
              , manage_code
              , manage_name
              , agent_code
              , agent_name
         from jms_dim.dim_network_whole_massage
     ), 
     result_table_tmp as (
         select t1.waybill_no                                              --运单号
              , t1.third_code                                              --三段码
              , t1.ordersource_code                                        --多平台订单来源编码
              , t1.ordersource_name                                        --多平的订单来源名称
              , t1.send_station_send_later_time                            --A路段始发站点发件扫描时间
              , t1.send_station_loading_send_later_shipment_no             --A路段始发站点任务号
              , t1.next_station_unloading_arrival_early_shipment_no        --B路段目的站点最早卸车到件扫描对应任务号
              , t1.next_station_send_packagecode                           --B路段目的站点发件扫描包号
              , t1.next_station_arrival_packagecode                        --B路段目的站点到件扫描包号
              , t1.next_station_arrival_scan_user_code                     --B路段目的站点到件扫描员CODE
              , t1.next_station_arrival_scan_user                          --B路段目的站点到件扫描员
              , t1.send_station_network_code                               --A路段始发站点
              , t1.send_station_network_type                               --A路段始发站点类型
              , t1.next_station_network_code                               --B路段目的站点
              , t1.next_station_network_type                               --B路段目的站点类型
              , t1.send_station_send_next_network_code                     --A路段始发站点发件扫描下一站点
              , t1.send_station_send_next_network_type                     --A路段始发站点发件扫描下一站点类型
              , t1.next_station_arrival_early_time                         --B路段目的站点最早到件时间
              , t1.next_station_send_later_time                            --B路段目的站点最晚发件时间
              , t1.sign_later_scantime                                     --最后一次签收时间
              , t1.next_next_station_network_code                          --B站点下一站
              , t1.have_arr_no_send_flag                                   --1代表有到未发
              , t1.have_send_no_arr_flag                                   --1代表有到未发
              , t1.date_time                                               --日期
              , t1.next_station_send_scan_user                             --B路段目的站点发件扫描员CODE'
              , t1.next_station_send_scan_user_code                        --B路段目的站点发件扫描员'
              , t1.other_scan_user_code                                    --到解车扫描雇员code
              , t1.other_scan_user                                         --到解车扫描雇员name
              , t1.unload_scan_time                                        --到解车扫描时间
              , t1.next_send_station_unloading_arrival_early_shipment_name --本站卸车任务号
              , t1.send_station_loading_send_later_shipment_name           --上一站装车任务号
              , t2.center_code                                             --到件拆包网点code
              , t2.center_name                                             --到件拆包网点name
              , t3.scan_site_code as arrival_pack_scan_site_code           --到件建包网点code
              , t3.scan_site      as arrival_pack_scan_site                --到件建包网点name
              , t3.scan_time      as arrival_pack_scan_time                --到件建包扫描时间
              , t4.scan_site_code as send_pack_scan_site_code              --发件建包网点code
              , t4.scan_site      as send_pack_scan_site                   --发件建包网点name
              , t7.name           as next_send_station_network_name        --中心名称
              , t7.agent_code     as agent_code                            --代理区code
              , t7.agent_name     as agent_name                            --代理区name
              , t7.manage_code    as manage_code                           --管理大区code
              , t7.manage_name    as manage_name                           --管理大区name
              , t8.name           as next_next_send_station_network_name   --B站点下一站name
              , t9.name           as send_station_network_name             --A站点上一站name
              , t1.next_next_station_unloading_arrival_early_shipment_no   --B站点下一站最早卸车到件扫描对应任务号
         from (
                  select waybill_no                                              --运单号
                       , third_code                                              --三段码
                       , ordersource_code                                        --多平台订单来源编码
                       , ordersource_name                                        --多平的订单来源名称
                       , send_station_send_later_time                            --A路段始发站点发件扫描时间
                       , send_station_loading_send_later_shipment_no             --A路段始发站点任务号
                       , next_station_unloading_arrival_early_shipment_no        --B路段目的站点最早卸车到件扫描对应任务号
                       , next_station_send_packagecode                           --B路段目的站点发件扫描包号
                       , next_station_arrival_packagecode                        --B路段目的站点到件扫描包号
                       , next_station_arrival_scan_user_code                     --B路段目的站点到件扫描员CODE
                       , next_station_arrival_scan_user                          --B路段目的站点到件扫描员
                       , send_station_network_code                               --A路段始发站点
                       , send_station_network_type                               --A路段始发站点类型
                       , next_station_network_code                               --B路段目的站点
                       , next_station_network_type                               --B路段目的站点类型
                       , send_station_send_next_network_code                     --A路段始发站点发件扫描下一站点
                       , send_station_send_next_network_type                     --A路段始发站点发件扫描下一站点类型
                       , next_station_arrival_early_time                         --B路段目的站点最早到件时间
                       , next_station_send_later_time                            --B路段目的站点最晚发件时间
                       , sign_later_scantime                                     --最后一次签收时间
                       , next_next_station_network_code                          --B站点下一站
                       , have_arr_no_send_flag                                   --1代表有到未发
                       , have_send_no_arr_flag                                   --1代表有到未发
                       , date_time                                               --日期
                       , next_station_send_scan_user                             --B路段目的站点发件扫描员CODE'
                       , next_station_send_scan_user_code                        --B路段目的站点发件扫描员'
                       , other_scan_user_code                                    --到解车扫描雇员code
                       , other_scan_user                                         --到解车扫描雇员name
                       , unload_scan_time                                        --到解车扫描时间
                       , next_send_station_unloading_arrival_early_shipment_name --本站卸车任务号
                       , send_station_loading_send_later_shipment_name           --上一站装车任务号
                       , next_next_station_unloading_arrival_early_shipment_no   --B站点下一站最早卸车到件扫描对应任务号
                  from jms_dm.dm_tab_cn_center_scan_lack_total_dt
                  where dt = '{{ execution_date | cst_ds }}'
                    and before_30_input <> 1 --运单录入时间超过30的会被剔除
                    and ((have_arr_have_send_flag <> 1 and have_arr_no_send_flag = 0 and have_send_no_arr_flag = 1)
                      or (have_arr_have_send_flag <> 1 and have_send_no_arr_flag = 0 and have_arr_no_send_flag = 1))
              ) t1
                  left join electronic_package t2
                            on t1.next_station_arrival_packagecode = t2.package_number --到件拆包网点
                  left join bagging_table t3
                            on t1.waybill_no = t3.waybill_no
                                and t1.next_station_arrival_packagecode = t3.package_code
                  left join bagging_table t4
                            on t1.waybill_no = t4.waybill_no
                                and t1.next_station_send_packagecode = t4.package_code
                  left join network_whole t7
                            on t1.next_station_network_code = t7.code
                  left join network_whole t8
                            on t1.next_next_station_network_code = t8.code
                  left join network_whole t9
                            on t1.send_station_network_code = t9.code 
     ),
     have_arrival_no_send as (--有到未发 装车发件漏扫
         select waybill_no                                                                 --运单号
              , third_code                                                                 --三段码
              , next_station_network_code                                                  --B中心code(到件)
              , next_send_station_network_name                                             --B中心code(到件)
              , agent_code                                                                 --B代理区(到件)
              , agent_name                                                                 --B代理区(到件)
              , manage_code                                                                --B管理大区code(到件)
              , manage_name                                                                --B管理大区name(到件)
              , send_station_network_code                                                  --A发件站点code,上一站
              , send_station_network_name                                                  --A发件站点name,上一站
              , null              as send_station_loading_send_later_shipment_no           --A站点 上一站 任务号
              , null              as send_station_loading_send_later_shipment_name         --A站点 上一站 任务号
              , next_station_unloading_arrival_early_shipment_no                           --B到件任务号
              , next_send_station_unloading_arrival_early_shipment_name                    --B到件任务号名称
              , next_station_arrival_early_time                                            --B到件时间(到件)
              , next_station_arrival_packagecode                                           --B到件包号(到件)
              , arrival_pack_scan_site_code                                                --B到件建包网点(到件)
              , arrival_pack_scan_site                                                     --B到件建包网点name(到件)
              , arrival_pack_scan_time                                                     --B到件建包扫描时间
              , center_code                                                                --B到件拆包网点code
              , center_name                                                                --B到件拆包网点name
              , next_station_arrival_scan_user_code                                        --B到件扫描员code
              , next_station_arrival_scan_user                                             --B到件扫描员name
              , null              as next_station_send_scan_user                           --B路段目的站点发件扫描员CODE'
              , null              as next_station_send_scan_user_code                      --B路段目的站点发件扫描员'
              , next_next_station_network_code                                             --B到件下一站code
              , next_next_send_station_network_name                                        --B到件下一站name
              , null              as next_send_station_send_later_time                     --B路段目的站点最晚发件时间
              , null              as next_station_send_packagecode                         --发件包号
              , null              as send_pack_scan_site_code                              --发件建包网点
              , null              as send_pack_scan_site                                   --发件建包网点
              , ordersource_code                                                           --多平台订单来源编码
              , ordersource_name                                                           --多平的订单来源名称
              , null              as other_scan_user_code                                  --到解车扫描雇员code
              , null              as other_scan_user                                       --到解车扫描雇员name
              , 1                 as type                                                  --1有到未发
              , date_time                                                                  --日期
              , t2.shipment_no    as next_next_station_unloading_arrival_early_shipment_no --B站点下一站最早卸车到件扫描对应任务号
              , t2.scan_time      as next_next_station_send_later_time                     --B站点下一站最晚发件时间
              , t2.scan_user      as next_next_station_send_scan_user                      --B站点下一站发件扫描员
              , t2.scan_user_code as next_next_station_send_scan_user_code                 --B站点下一站发件扫描员code
         from result_table_tmp t1
                  left join load_base_ship t2
                            on t1.next_station_network_code = t2.scan_site_code
                                and t1.next_next_station_unloading_arrival_early_shipment_no = t2.shipment_no
                                and t1.next_station_arrival_early_time < t2.scan_time
         where have_arr_no_send_flag = 1
           and have_send_no_arr_flag = 0
     ),
     have_send_no_arrival as (--有发未到  卸车到件漏扫
         select waybill_no                                                        --运单号
              , third_code                                                        --三段码
              , next_station_network_code                                         --B中心code(到件)
              , next_send_station_network_name                                    --B中心code(到件)
              , agent_code                                                        --B代理区(到件)
              , agent_name                                                        --B代理区(到件)
              , manage_code                                                       --B管理大区code(到件)
              , manage_name                                                       --B管理大区name(到件)
              , send_station_network_code                                         --A发件站点code,上一站
              , send_station_network_name                                         --A发件站点name,上一站
              , send_station_loading_send_later_shipment_no                       --A站点 上一站 任务号
              , send_station_loading_send_later_shipment_name                     --A站点 上一站 任务号
              , null     as next_station_unloading_arrival_early_shipment_no      --B到件任务号
              , null     as next_station_unloading_arrival_early_shipment_name    --B到件任务号名称
              , null     as next_send_station_arrival_early_time                  --B到件时间(到件)
              , null     as next_station_arrival_packagecode                      --B到件包号(到件)
              , null     as arrival_pack_scan_site_code                           --B到件建包网点(到件)
              , null     as arrival_pack_scan_site                                --B到件建包网点name(到件)
              , null     as arrival_pack_scan_time                                --B到件建包扫描时间
              , null     as center_code                                           --B到件拆包网点code
              , null     as center_name                                           --B到件拆包网点name
              , null     as next_station_arrival_scan_user_code                   --B到件扫描员code
              , null     as next_station_arrival_scan_user                        --B到件扫描员name
              , next_station_send_scan_user                                       --B路段目的站点发件扫描员CODE'
              , next_station_send_scan_user_code                                  --B路段目的站点发件扫描员'
              , next_next_station_network_code                                    --B到件下一站code
              , next_next_send_station_network_name                               --B到件下一站name
              , next_station_send_later_time                                      --B装车发件时间
              , next_station_send_packagecode                                     --发件包号
              , send_pack_scan_site_code                                          --发件建包网点code
              , send_pack_scan_site                                               --发件建包网点name
              , ordersource_code                                                  --多平台订单来源编码
              , ordersource_name                                                  --多平的订单来源名称
              , if(send_station_send_later_time < unload_scan_time, other_scan_user_code,
                   null) as other_scan_user_code                                  --到解车扫描雇员code
              , if(send_station_send_later_time < unload_scan_time is not null, other_scan_user,
                   null) as other_scan_user                                       --到解车扫描雇员name
              , 2        as type                                                  --有发未到
              , date_time                                                         --日期
              , null     as next_next_station_unloading_arrival_early_shipment_no --B站点下一站最早卸车到件扫描对应任务号
              , null     as next_next_station_send_later_time                     --B站点下一站最晚发件时间
              , null     as next_next_station_send_scan_user                      --B站点下一站发件扫描员
              , null     as next_next_station_send_scan_user_code                 --B站点下一站发件扫描员code
         from result_table_tmp
         where have_arr_no_send_flag = 0
           and have_send_no_arr_flag = 1
     )
insert
overwrite
table
jms_dm.dm_tab_cn_center_scan_lack_detail_dt
partition
(
dt
)
select waybill_no                                              as billcode                    --运单号
     , third_code                                              as terminal_dispatch_code      --三段码
     , next_station_network_code                               as scan_code                   --B中心code(到件)
     , next_send_station_network_name                          as scan_name                   --B中心code(到件)
     , agent_code                                              as agent_code                  --B代理区(到件)
     , agent_name                                              as agent_name                  --B代理区(到件)
     , manage_code                                             as mage_region_code            --B管理大区code(到件)
     , manage_name                                             as mage_region_name            --B管理大区name(到件)
     , null                                                    as arrival_cnt                 --到件次数次数为0为到件漏扫  原表有,现在逻辑没有%%%%&
     , next_station_arrival_early_time                         as arrival_time                --B到件时间(到件)
     , next_station_arrival_packagecode                        as arrival_packagecode         --B到件包号(到件)
     , arrival_pack_scan_time                                  as arrival_pack_create_time    --B到件建包扫描时间
     , arrival_pack_scan_site_code                             as arrival_pack_start_code     --B到件建包网点(到件)
     , arrival_pack_scan_site                                  as arrival_pack_start_name     --B到件建包网点name(到件)
     , center_code                                             as arrival_pack_end_code       --B到件拆包网点code
     , center_name                                             as arrival_pack_end_name       --B到件拆包网点name 
     , next_station_unloading_arrival_early_shipment_no        as arrival_transfercode        --A站点 上一站 任务号
     , next_send_station_unloading_arrival_early_shipment_name as arrival_shipment_name       --A站点 上一站 任务号
     , next_station_arrival_scan_user_code                     as arrival_scanusercode        --B到件扫描员code
     , next_station_arrival_scan_user                          as arrival_scanuser            --B到件扫描员name
     , null                                                    as send_cnt                    --发件次数次数为0为发件漏扫   原表有,现在逻辑没有%%%%&
     , next_send_station_send_later_time                       as send_time                   --B路段目的站点最晚发件时间
     , next_station_send_packagecode                           as send_packagecode            --发件包号
     , send_pack_scan_site_code                                as send_pack_start_code        --发件建包网点code
     , send_pack_scan_site                                     as send_pack_start_name        --发件建包网点name
     , next_station_send_scan_user_code                        as send_scanusercode           --B路段目的站点发件扫描员CODE'
     , next_station_send_scan_user                             as send_scanuser               --B路段目的站点发件扫描员'
     , send_station_network_code                               as lst_scan_code               --A发件站点code,上一站
     , send_station_network_name                               as lst_scan_name               --A发件站点name,上一站
     , next_next_station_network_code                          as nxt_scan_code               --B到件下一站code
     , next_next_send_station_network_name                     as nxt_scan_name               --B到件下一站name
     , null                                                    as send_lack_packagecode       --发件漏扫包号          原表有,现在逻辑没有%%%%&
     , null                                                    as send_lack_pack_start_code   --发件漏扫建包网点编码   原表有,现在逻辑没有%%%%&
     , null                                                    as send_lack_pack_start_name   --发件漏扫建包网点名称   原表有,现在逻辑没有%%%%&
     , ordersource_code                                        as order_source_code           --多平台订单来源编码
     , ordersource_name                                        as order_source_name           --多平的订单来源名称 
     , send_station_loading_send_later_shipment_no             as lst_scan_code_shipment_no   --B到件任务号
     , send_station_loading_send_later_shipment_name           as lst_scan_code_shipment_name --B到件任务号名称
     , other_scan_user_code                                    as other_scan_user_code        --到解车扫描雇员code
     , other_scan_user                                         as other_scan_user             --到解车扫描雇员name
     , date_time                                               as date_time                   --日期
     , type                                                    as type                        --1有到未发
     , next_next_station_unloading_arrival_early_shipment_no                                  --B站点下一站最早卸车到件扫描对应任务号
     , next_next_station_send_later_time                                                      --B站点下一站最晚发件时间
     , next_next_station_send_scan_user                                                       --B站点下一站发件扫描员
     , next_next_station_send_scan_user_code                                                  --B站点下一站发件扫描员code
     , date_time                                               as dt
from (
    select *
    from have_arrival_no_send
    union all
    select *
    from have_send_no_arrival
) a
    distribute by dt ;

